package bookManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by xiebang
 * Description
 * User:小谢
 * Date:2021-11-30
 * Time:10:14
 *
 * 如何保证代码没问题,从理论上说保证不了
 * 能做的只能是多多的去进行测试.
 */
//这个类负责提供BOOK表的增删查改

public class BookDAO {
    //新增一本书
    public void add(Book book) /*throws SQLException*/ {
        //和数据库建立连接
        Connection connection = null;
        PreparedStatement statement = null;
        //一旦抛出异常,就会跳转到catch,close执行不到,不能回收资源
        try {
            connection = DBUtil.getConnection();
            String sql = "insert into book values(null,?,?,?,?,0)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,book.getName());
            statement.setString(2,book.getAuthor());
            statement.setInt(3,book.getPrice());
            statement.setString(4,book.getType());

            int ret = statement.executeUpdate();
            if (ret != 1){
                System.out.println("新增图书失败!!!");
            }else {
                System.out.println("新增图书成功!!!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }


    }

//    // 查找所有的书
//    public List<Book> findBooks(){
//        Connection connection = null;
//        PreparedStatement statement = null;
//        ResultSet resultSet = null;
//        List<Book> list = new ArrayList<>();
//        try {
//            connection = DBUtil.getConnection();
//            String sql = "select * from book";
//            statement = connection.prepareStatement(sql);
//            resultSet = statement.executeQuery();
//            if (resultSet!=null) {
//                while (resultSet.next()) {
//                    int id = resultSet.getInt("bookId");
//                    String name = resultSet.getString("name");
//                    String author = resultSet.getString("author");
//                    int price = resultSet.getInt("price");
//                    String type = resultSet.getString("type");
//                    int isborrowed = resultSet.getInt("isborrowed");
////                    System.out.println("bookId: " + id + " name: " + name + " price: " + price + " type: " + type + " isborrowed: " + isborrowed);
//                    Book book = new Book(id, name, author, price, type, isborrowed == 1);
//                    list.add(book);
//                }
//            }else {
//                System.out.println("查询失败!");
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            DBUtil.close(connection,statement,resultSet);
//        }
//        return list;
//    }
// 查找所有的书
public List<Book> findBooks(){
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Book> books = new ArrayList<>();
    try {
        connection = DBUtil.getConnection();
        String sql = "select * from book";
        statement = connection.prepareStatement(sql);
        resultSet = statement.executeQuery();
        if (resultSet!=null) {
            while (resultSet.next()) {
                Book book = new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setB0rrowed(resultSet.getInt("isborrowed") == 1);
                books.add(book);
            }
        }else {
            System.out.println("查询失败!");
            return null;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        DBUtil.close(connection,statement,resultSet);
    }
    return books;
}

    //根据书名来查找书
    public List<Book> findBooksByName(String name){
        List<Book> books = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from book where name = ?";

            statement = connection.prepareStatement(sql);
            statement.setString(1,name);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                    Book book = new Book();
                    book.setBookId(resultSet.getInt("bookId"));
                    book.setName(resultSet.getString("name"));
                    book.setAuthor(resultSet.getString("author"));
                    book.setPrice(resultSet.getInt("price"));
                    book.setType(resultSet.getString("type"));
                    book.setB0rrowed(resultSet.getInt("isborrowed") == 1);

                    books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return books;
    }

    //根据id来删除书
    public boolean delete(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtil.getConnection();

            String sql = "delete from book where bookId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);

            int ret = statement.executeUpdate();
            if (ret != 1){
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }
    //借书
    public boolean borrowBook(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        PreparedStatement statement1 = null;

        try {
            connection = DBUtil.getConnection();

            String sql = "select * from book where bookId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);

            resultSet = statement.executeQuery();
            if (resultSet.next()){
                boolean isborrowed = resultSet.getInt("isborrowed") == 1;
                if (isborrowed){
                    System.out.println("数已经被借出了! bookId = " + bookId);
                    return false;
                }

            }else {
                System.out.println("没有这本书! bookId = " + bookId);
                return false;
            }

            String sql1 = "update book set isborrowed = 1 where bookId = ?";
            statement1 = connection.prepareStatement(sql1);
            statement1.setInt(1,bookId);
            int ret = statement1.executeUpdate();
            if (ret != 1){
                return false;
            }

            return true;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (statement1 != null){
                try {
                    statement1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
//            DBUtil.close(connection,statement,resultSet);
        }
        return false;
    }
    //还书

    public boolean returnBook(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        PreparedStatement statement1 = null;
        try {
            connection = DBUtil.getConnection();

            String sql = "select * from book where bookId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);

            resultSet = statement.executeQuery();
            if (resultSet.next()){
                boolean isborrowed = resultSet.getInt("isborrowed") == 1;
                if (!isborrowed){
                    System.out.println("数已经被归还了! bookId = " + bookId);
                    return false;
                }
            }else {
                System.out.println("没有这本书! bookId = " + bookId);
                return false;
            }
            String sql1 = "update book set isborrowed = 0 where bookId = ?";
            statement1 = connection.prepareStatement(sql1);
            statement1.setInt(1,bookId);
            int ret = statement1.executeUpdate();
            if (ret != 1){
                return false;
            }

            return true;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (statement1 != null){
                try {
                    statement1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
//            DBUtil.close(connection,statement,resultSet);
        }
        return false;
    }

    public static void main(String[] args) {
        //1.验证插入书籍
//        BookDAO bookDAO = new BookDAO();
//        Book book = new Book();
//        book.setName("水浒传");
//        book.setAuthor("施耐庵");
//        book.setType("古典小说");
//        book.setPrice(10000);
//        bookDAO.add(book);

        //验证查找
//        BookDAO bookDAO =new BookDAO();
//        List<Book> books = bookDAO.findBooks();
//        System.out.println(books);

        //验证id查找
//        BookDAO bookDAO =new BookDAO();
//        List<Book> books = bookDAO.findBooksByName("三国演义");
//        System.out.println(books);

        // 删除
//        BookDAO bookDAO =new BookDAO();
//        boolean ret = bookDAO.delete(5);
//        System.out.println(ret);

        // 借书
//        BookDAO bookDAO =new BookDAO();
//        boolean ret = bookDAO.borrowBook(1);
//        System.out.println(ret);

        //还书
        BookDAO bookDAO =new BookDAO();
        boolean ret = bookDAO.returnBook(1);
        System.out.println(ret);
        //冒烟测试
    }
}
